using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Model;
using SEO.DBSqlHelp;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMMailGroupDAL
    /// 完成日期：2011年7月29日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMMailGroupDAL
    {

        /// <summary>
        /// 获取EDMMailGroup表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<EDMMailGroup> GetAllEDMMailGroupDAL()
        {
            List<EDMMailGroup> list = new List<EDMMailGroup>();
            string strSql = "select * from EDMMailGroup";
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailGroup ep = new EDMMailGroup();
                    ep.GroupId = int.Parse(dr["group_id"].ToString());
                    ep.GroupName = dr["group_name"].ToString();
                    ep.GroupNum = int.Parse(dr["group_num"].ToString());
                    ep.UserId = int.Parse(dr["user_id"].ToString());
                    ep.PostStatus = dr["post_status"].ToString();

                    list.Add(ep);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取EDMMailGroup表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<EDMMailGroup> GetEDMMailGroupByWhereDAL(string strSql)
        {
            List<EDMMailGroup> list = new List<EDMMailGroup>();
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailGroup ep = new EDMMailGroup();
                    ep.GroupId = int.Parse(dr["group_id"].ToString());
                    ep.GroupName = dr["group_name"].ToString();
                    ep.GroupNum = int.Parse(dr["group_num"].ToString());
                    ep.UserId = int.Parse(dr["user_id"].ToString());
                    ep.PostStatus = dr["post_status"].ToString();

                    list.Add(ep);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往EDMMailGroup表中插入数据
        /// <param name="ep">实例化EDMMailGroup类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertEDMMailGroupDAL(SEO.Model.EDMMailGroup ep)
        {
            int iNum = 0;
            string strSql = "insert into EDMMailGroup(group_name,group_num,user_id) " +
             " values('" + ep.GroupName + "'," + ep.GroupNum + "," + ep.UserId + ")";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除EDMMailGroup表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteEDMMailGroupDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新EDMMailGroup表中数据
        /// </summary>
        /// <param name="ep">实例化EDMMailGroup类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMMailGroupDAL(SEO.Model.EDMMailGroup ep)
        {
            int iNum = 0;
            string strSql = "update EDMMailGroup set group_num=" + ep.GroupNum + " where group_name='" + ep.GroupName + "' and user_id=" + ep.UserId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return iNum;
        }

        public int UpdateEDMMailGroupNewDAL(SEO.Model.EDMMailGroup ep)
        {
            int iNum = 0;
            string strSql = "update EDMMailGroup set group_name='" + ep.GroupName + "',post_status='" + ep.PostStatus + "' where group_id=" + ep.GroupId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表EDMMailGroup到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindEDMMailGroupDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMMailGroup");
                ds.Tables["EDMMailGroup"].Columns.Add("ShowDate", typeof(string));
                ds.Tables["EDMMailGroup"].Columns.Add("PostStatus", typeof(string));
                for (int i = 0; i < ds.Tables["EDMMailGroup"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EDMMailGroup"].Rows[i];
                    if (dr["post_status"].ToString() == "1")
                    {
                        dr["PostStatus"] = "未加入发布队列";
                    }
                    else
                    {
                        dr["PostStatus"] = "已加入发布队列";
                    }
                    dr["ShowDate"] = Convert.ToDateTime(dr["add_date"].ToString()).ToString("yyyy.MM.dd");
                }
                dgv.DataSource = ds.Tables["EDMMailGroup"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
        }

        /// <summary>
        /// 检测是否已存在数据库中
        /// </summary>
        /// <param name="strGroupName">分组名称</param>
        /// <returns>返回 True or False</returns>
        public bool CheckMailGroupIsExist(string strGroupName)
        {
            bool bValue = false;
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "select group_id from EDMMailGroup where group_name='" + strGroupName + "'";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 填充ComboBoxEx控件
        /// </summary>
        /// <param name="cb">ComboBoxEx控件</param>
        /// <param name="TableName">表名</param>
        /// <param name="strValue">填充字段</param>
        public void FillDataToComboBox(System.Windows.Forms.ComboBox cb, string TableName, string strValue)
        {
            SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
            cn.Open();
            string strSql = "select " + strValue + " from " + TableName;
            SqlCommand cm = new SqlCommand(strSql, cn);
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                string strName = dr[strValue].ToString();
                cb.Items.Add(strName);
            }
            dr.Dispose();
            cn.Close();
        }

        /// <summary>
        /// 根据分组名称获取分组ID
        /// </summary>
        /// <param name="strGroupName"></param>
        /// <param name="strUserID"></param>
        /// <returns></returns>
        public int GetMailGroupIDByGroupName(string strGroupName,string strUserID)
        {
            int iGroupID = 0;
            SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
            cn.Open();
            string strSql = "select group_id from EDMMailGroup where group_name='" + strGroupName + "' and user_id=" + strUserID;
            SqlCommand cm = new SqlCommand(strSql, cn);
            SqlDataReader dr = cm.ExecuteReader();
            if (dr.Read())
            {
                iGroupID = int.Parse(dr["group_id"].ToString());
            }
            else
            {
                iGroupID = 1;
            }
            dr.Dispose();
            cn.Close();

            return iGroupID;
        }


    }
}
